package com.ndpmedia.fb.pmd.configration.db;

import com.ndpmedia.fb.pmd.configration.base.annotation.DBField;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;

/**
 * baseDao
 * 
 * @author rock
 * 
 */
@Service
public class BasePepository {
	/**
	 * 日志对象
	 */
	private static Logger logger = Logger.getLogger(BasePepository.class);

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int[] batchUpdate(String sql, BatchPreparedStatementSetter setter) {
		return jdbcTemplate.batchUpdate(sql, setter);
	}
	
	/**
	 * 注意：这里的sql不支持 命名参数，只能用 ? 代替
	 * 
	 * @param sql
	 *            update ca_user set a =? ,b=? where c = ?
	 * @param dataSet
	 *            List<String[]>
	 * @return
	 */
	public int[] batchUpdate(String sql, final List<String[]> dataSet) {
		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return dataSet.size();
			}

			public void setValues(PreparedStatement psmt, int i) {
				String[] obj = dataSet.get(i);
				try {
					for (int j = 0; j < obj.length; j++) {
						psmt.setString(j + 1, obj[j]);
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		};
		return jdbcTemplate.batchUpdate(sql, setter);
	}

	/**
	 * 更新sql语句的执行，没有参数的情况
	 * 
	 * @param sql
	 * @return 受影响的行数
	 */
	public boolean update(String sql) {
		return jdbcTemplate.update(sql) > 0;
	}

	/**
	 * 直接执行一条sql
	 * 
	 * @param sql
	 */
	public void execute(String sql) {
		jdbcTemplate.execute(sql);
	}

	/**
	 * 可直接运行的sql语句数组
	 * 
	 * @param sql
	 *            update ca_user set a ='newea' ,b=3 where c = 5
	 * @return
	 */
	public int[] batchUpdate(String[] sql) {
		return jdbcTemplate.batchUpdate(sql);
	}

	/**
	 * 更新语句执行
	 * 
	 * @param sql
	 *            ：更新语句
	 * @param paramMap
	 *            ：命名参数
	 * @return 受影响的行数
	 */
	public boolean update(String sql, Map paramMap) {
		getLogInfo(sql, paramMap);
		return jdbcTemplate.update(sql, new MapSqlParameterSource(paramMap)) > 0;
	}



	/**
	 * 查询单列(类型为整型)，返回一个整数值<br>
	 * 多个参数的情况
	 * 
	 * @param sql
	 *            ：select count(*) from demo where a =: a and b =: b<br>
	 *            select nid from demo where a =: a and b =: b ;其中nid 为数值型
	 * @param paramMap
	 *            命名参数为key；参数值为value的Map
	 * @return 整数值
	 */
	public int queryForInt(String sql, Map paramMap) {
		getLogInfo(sql, paramMap);
		int i = -1;
		try {
			i = jdbcTemplate.queryForInt(sql, new MapSqlParameterSource(
					paramMap));
		} catch (EmptyResultDataAccessException ex) {
			logger.error("忽略此类错误[EmptyResultDataAccessException],允许查询为空时,返回-1!");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return i;
	}

	/**
	 * 没有参数的情况，查询单列(类型为整型)，返回一个整数值<br>
	 * 
	 * @param sql
	 *            ：select count(*) from demo where a =: a and b =: b<br>
	 *            select nid from demo where a =: a and b =: b ;其中nid 为数值型
	 * @return 整数值
	 */
	public int queryForInt(String sql) {
		int i = -1;
		try {
			i = jdbcTemplate.queryForInt(sql);
		} catch (EmptyResultDataAccessException ex) {
			logger.error("忽略此类错误[EmptyResultDataAccessException],允许查询为空时,返回-1!");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return i;
	}

	/**
	 * 查询单列(类型为整型)，返回一个整数值<br>
	 * 
	 * @param sql
	 *            ：select count(*) from demo where a =: a and b =: b <br>
	 *            select nid from demo where a =: a and b =: b ;其中nid 为数值型
	 * @param paramMap
	 *            命名参数为key；参数值为value的Map
	 * @return 长整数值
	 */
	public long queryForLong(String sql, Map<String, ?> paramMap) {
		getLogInfo(sql, paramMap);
		long l = -1L;
		try {
			l = jdbcTemplate.queryForLong(sql, new MapSqlParameterSource(
					paramMap));
		} catch (EmptyResultDataAccessException ex) {
			logger.error("忽略此类错误[EmptyResultDataAccessException],允许查询为空时,返回-1!");
		}
		return l;
	}

	/**
	 * 查询单列(类型为整型)，返回一个整数值<br>
	 * 
	 * @param sql
	 *            ：select count(*) from demo where a =: a and b =: b <br>
	 *            select nid from demo where a =: a and b =: b ;其中nid 为数值型
	 * @return 长整数值
	 */
	public long queryForLong(String sql) {
		return queryForLong(sql, null);
	}

	/**
	 * 打印相应的日志信息
	 * 
	 * @param sql
	 * @param paramMap
	 *            ：命名参数值对
	 */
	protected final void getLogInfo(String sql, Map<String, ?> paramMap) {
		try {
			if (logger.isInfoEnabled()) {
				StringBuffer sbuf = new StringBuffer(" JDBC查询的sql语句是：")
						.append(sql);
				if (null == paramMap) {
					sbuf.append(" ; 参数为空!");
				} else {
					sbuf.append("\n 命名参数以及对应的数值:\n");
					Set<String> keys = paramMap.keySet();
					for (String key : keys) {
						sbuf.append("  {").append(key).append("} = ")
								.append(paramMap.get(key)).append("\n");
					}
				}
				logger.info(sbuf);
			}
		} catch (Exception ex) {
			logger.error("日志打印出现错误：" + ex.getMessage());
		}
	}

	private <T> T getResult(SqlRowSet rs, Class<T> clazz) {
		T t = null;
		try {
			t = clazz.newInstance();
			Field[] fields = clazz.getDeclaredFields();
			for (Field field : fields) {

				Annotation[] anns = field.getDeclaredAnnotations();
				for (Annotation anno : anns) {
					if (anno instanceof DBField) {
						String dbFieldName = ((DBField) anno).value();
						Object obj = null;
						try {
							obj = rs.getObject(dbFieldName);
						} catch (Exception e) {
						}
						if (null != obj) {
							StringBuffer methodName = new StringBuffer();
							String fieldName = field.getName();
							String firstChar = fieldName.substring(0, 1)
									.toUpperCase(Locale.getDefault());
							String otherChar = fieldName.substring(1);
							methodName
									.append("set");
							methodName.append(firstChar);
							methodName.append(otherChar);
							Method method;
							method = clazz.getMethod(methodName.toString(),
									field.getType());
							if ("java.lang.String".equals(field.getType()
									.getName())) {
								method.invoke(t, String.valueOf(obj));
							} else if ("java.lang.Integer".equals(field
									.getType().getName())) {
								method.invoke(t, Integer.valueOf(String
										.valueOf(obj)));
							} else if ("java.lang.Double".equals(field
									.getType().getName())) {
								method.invoke(t, Double.valueOf(String
										.valueOf(obj)));
							} else if ("java.lang.Long".equals(field
									.getType().getName())) {
								method.invoke(t, Long.valueOf(String
										.valueOf(obj)));
							}
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return t;
	}

	protected <T> List<T> queryForList(String sql, Class<T> clazz) {
		// 日志打印
		List<T> list = jdbcTemplate.queryForList(sql, clazz);
		return list;
	}

	protected <T> List<T> queryForListObject(String sql, final Class<T> clazz) {
		List<T> list = new ArrayList<T>();
		try {
			SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql);
			while (sqlRowSet.next()) {
				T t = getResult(sqlRowSet, clazz);
				if (null != t) {
					list.add(t);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

}
